
package com.rasenhund.android.grecorder.dao;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import android.content.ContentValues;
import android.database.sqlite.SQLiteCursor;
import android.database.sqlite.SQLiteDatabase;

import com.rasenhund.android.grecorder.common.Const;
import com.rasenhund.android.grecorder.schema.CategoryCount;
import com.rasenhund.android.grecorder.schema.Record;
import com.rasenhund.android.grecorder.schema.StageRecord;

/**
 * 戦闘履歴 Data Access Objectクラス
 */
public class RecordDao {
    // @SuppressWarnings("unused")
    private static final String TAG = RecordDao.class.getSimpleName();
    // private final BattleRecordDao self = this;

    private SQLiteDatabase mDatabase;

    // テーブル名
    public static final String TABLE_NAME = "BattleRecord";

    // カラム名
    public class Columns {
        public static final String ROW_ID = "_id"; // ID(PK)
        public static final String STAGE_CD = "stageCd"; // ステージコード
        public static final String CATEGORY_CD = "categoryCd"; // カテゴリコード
        public static final String RESULT_CD = "resultCd"; // 結果コード
        public static final String INSERT_DATE = "insertDate"; // 登録日
        // ver 0.9.4 > 0.9.5にて項目追加(DB version 1 > 2)
        public static final String VERSUS = "versus"; // 対戦人数
        public static final String POINT = "point"; // ポイント
        public static final String RANK_CD = "rankCd"; // 戦闘ランク
        public static final String MEMO = "memo"; // メモ
        public static final String EXCLUDE_FLG = "excludeFlg"; // 集計対象外フラグ
    }

    /**
     * Constractor
     * 
     * @param db
     */
    public RecordDao(SQLiteDatabase db) {
        mDatabase = db;
    }

    /********************
     * Select
     ********************/

    public Record selectAt(int rowId) {

        Record record = null;
        SQLiteCursor cursor = null;

        String[] columns = {
                Columns.ROW_ID,
                Columns.STAGE_CD,
                Columns.CATEGORY_CD,
                Columns.RESULT_CD,
                Columns.INSERT_DATE,
                Columns.VERSUS,
                Columns.POINT,
                Columns.RANK_CD,
                Columns.MEMO,
                Columns.EXCLUDE_FLG
        };

        String sWhere = Columns.ROW_ID + " = " + rowId;

        try {

            cursor = (SQLiteCursor) mDatabase.query(TABLE_NAME, columns, sWhere, null, null, null,
                    null);
            cursor.moveToFirst();

            if (cursor.getCount() > 0) {

                record = new StageRecord();

                record.setRowId(cursor.getLong(0));
                record.setStageCd(cursor.getInt(1));
                record.setCategoryCd(cursor.getInt(2));
                record.setResultCd(cursor.getInt(3));
                record.setInsertDate(cursor.getString(4));
                record.setVersus(cursor.getInt(5));
                record.setPoint(cursor.getInt(6));
                record.setRankCd(cursor.getInt(7));
                record.setMemo(cursor.getString(8));
                // stage.setMemoFromBase64(cursor.getString(8));
                record.setExcludeFlg(cursor.getInt(9));

            }

        } catch (Exception e) {

            // Log.e(TAG, "RecordDao.selectAll failed - " + e.toString());

        } finally {

            if (cursor != null) {
                cursor.close();
            }

        }

        return record;
    }

    /**
     * 全件取得(昇順)
     * 
     * @return
     */
    public ArrayList<StageRecord> selectAll() {
        return selectAll(true);
    }

    /**
     * Orderを指定して全件取得
     * 
     * @param bIsAsc 昇順の場合True
     * @return
     */
    public ArrayList<StageRecord> selectAll(Boolean bIsAsc) {

        ArrayList<StageRecord> list = new ArrayList<StageRecord>();
        StageRecord stage = null;

        SQLiteCursor cursor = null;

        String[] columns = {
                Columns.ROW_ID,
                Columns.STAGE_CD, 
                Columns.CATEGORY_CD, 
                Columns.RESULT_CD, 
                Columns.INSERT_DATE,
                Columns.VERSUS,
                Columns.POINT,
                Columns.RANK_CD,
                Columns.MEMO,
                Columns.EXCLUDE_FLG
        };

        String sOrderBy = "";
        if (bIsAsc) {
            sOrderBy = Columns.ROW_ID + " ASC";
        } else {
            sOrderBy = Columns.ROW_ID + " DESC";
        }

        try {

            cursor = (SQLiteCursor) mDatabase.query(TABLE_NAME, columns, null, null, null, null,
                    sOrderBy);
            cursor.moveToFirst();

            if (cursor.getCount() > 0) {

                for (int i = 0; i < cursor.getCount(); i++) {

                    stage = new StageRecord();

                    stage.setRowId(cursor.getLong(0));
                    stage.setStageCd(cursor.getInt(1));
                    stage.setCategoryCd(cursor.getInt(2));
                    stage.setResultCd(cursor.getInt(3));
                    stage.setInsertDate(cursor.getString(4));
                    stage.setVersus(cursor.getInt(5));
                    stage.setPoint(cursor.getInt(6));
                    stage.setRankCd(cursor.getInt(7));
                    stage.setMemo(cursor.getString(8));
                    stage.setExcludeFlg(cursor.getInt(9));

                    list.add(stage);

                    cursor.moveToNext();

                }

            }

        } catch (Exception e) {

            // Log.e(TAG, "RecordDao.selectAll failed - " + e.toString());

        } finally {

            if (cursor != null) {
                cursor.close();
            }

        }

        return list;

    }

    /**
     * 戦績データの取得
     * 
     * @param stage
     */
    public void getResult(StageRecord stage) {

        SQLiteCursor cursor = null;
        int iStageId = stage.getStageCd();

        // @formatter:off
        String sQuery = 
            " select " + 
            " COUNT(" + Columns.ROW_ID + ") " +
            " , SUM( CASE " + Columns.RESULT_CD + 
            "             WHEN " + Const.RESULT.WIN + " THEN 1 " + 
            "             WHEN " + Const.RESULT.PERFECT_WIN + " THEN 1 " + 
            "             ELSE 0 " +
            "        END ) " + 
            " , SUM( CASE " + Columns.RESULT_CD + 
            "             WHEN " + Const.RESULT.LOSE + " THEN 1 " + 
            "             WHEN " + Const.RESULT.PERFECT_LOSE + " THEN 1 " + 
            "             ELSE 0 " + 
            "        END ) " + 
            " , SUM( CASE " + Columns.RESULT_CD + " WHEN " + Const.RESULT.DRAW + " THEN 1 ELSE 0 END ) " + 
            "   from " + TABLE_NAME + 
            "  where " + Columns.STAGE_CD + " = " + iStageId + 
            "  group by " + Columns.STAGE_CD;
        // @formatter:on

        try {

            cursor = (SQLiteCursor) mDatabase.rawQuery(sQuery, null);
            cursor.moveToFirst();

            if (cursor.getCount() > 0) {

                stage.setTotalCount(cursor.getInt(0));
                stage.setWinCount(cursor.getInt(1));
                stage.setLoseCount(cursor.getInt(2));
                stage.setDrawCount(cursor.getInt(3));

            }

        } catch (Exception e) {

            // Log.e(TAG, "RecordDao.getResult failed - " + e.toString());

        } finally {

            if (cursor != null) {
                cursor.close();
            }

        }

        return;

    }

    /**
     * ステージ毎、カテゴリ毎の戦績データの取得
     * 
     * @param stage
     */
    public ArrayList<StageRecord> getResultByCategory(int stageId) {

        // TODO:
        ArrayList<StageRecord> list = new ArrayList<StageRecord>();
        StageRecord stage = null;

        SQLiteCursor cursor = null;

        // @formatter:off
        String sQuery = 
            " select " + Columns.CATEGORY_CD + 
            " , COUNT(" + Columns.ROW_ID + ") " +
            " , SUM( CASE " + Columns.RESULT_CD + 
            "             WHEN " + Const.RESULT.WIN + " THEN 1 " + 
            "             WHEN " + Const.RESULT.PERFECT_WIN + " THEN 1 " + 
            "             ELSE 0 " +
            "        END ) " + 
            " , SUM( CASE " + Columns.RESULT_CD + 
            "             WHEN " + Const.RESULT.LOSE + " THEN 1 " + 
            "             WHEN " + Const.RESULT.PERFECT_LOSE + " THEN 1 " + 
            "             ELSE 0 " + 
            "        END ) " + 
            " , SUM( CASE " + Columns.RESULT_CD + " WHEN " + Const.RESULT.DRAW + " THEN 1 ELSE 0 END ) " + 
            "   from " + TABLE_NAME + 
            "  where " + Columns.STAGE_CD + " = " + stageId + 
            "  group by " + Columns.STAGE_CD + ", " + Columns.CATEGORY_CD;
        // @formatter:on

        try {

            cursor = (SQLiteCursor) mDatabase.rawQuery(sQuery, null);
            cursor.moveToFirst();

            if (cursor.getCount() > 0) {

                for (int i = 0; i < cursor.getCount(); i++) {

                    stage = new StageRecord();

                    stage.setStageCd(stageId);
                    stage.setCategoryCd(cursor.getInt(0));
                    stage.setTotalCount(cursor.getInt(1));
                    stage.setWinCount(cursor.getInt(2));
                    stage.setLoseCount(cursor.getInt(3));
                    stage.setDrawCount(cursor.getInt(4));

                    list.add(stage);

                    cursor.moveToNext();

                }

            }

        } catch (Exception e) {

            // Log.e(TAG, "RecordDao.getResultByCategory failed - " +
            // e.toString());

        } finally {

            if (cursor != null) {
                cursor.close();
            }

        }

        return list;

    }

    /**
     * カテゴリ別出撃回数データの取得
     * 
     * @return
     */
    public CategoryCount getCategoryCount() {
        return getCategoryCount(0);
    }


    /**
     * カテゴリ別出撃回数データの取得(先頭n件)
     * 
     * @param top
     * @return
     */
    public CategoryCount getCategoryCount(int top) {

        SQLiteCursor cursor = null;
        CategoryCount categoryCount = null;

        // @formatter:off
        String sQuery = 
            " SELECT COUNT(" + Columns.ROW_ID + ") " +
            "      , SUM( CASE " + Columns.CATEGORY_CD + " WHEN " + Const.CATEGORY.GRAPP + " THEN 1 ELSE 0 END ) " + 
            "      , SUM( CASE " + Columns.CATEGORY_CD + " WHEN " + Const.CATEGORY.PROX + " THEN 1 ELSE 0 END ) " + 
            "      , SUM( CASE " + Columns.CATEGORY_CD + " WHEN " + Const.CATEGORY.SHOOT + " THEN 1 ELSE 0 END ) " + 
            "      , SUM( CASE " + Columns.CATEGORY_CD + " WHEN " + Const.CATEGORY.CANNON + " THEN 1 ELSE 0 END ) " + 
            "      , SUM( CASE " + Columns.CATEGORY_CD + " WHEN " + Const.CATEGORY.SNIPE + " THEN 1 ELSE 0 END ) " + 
            "   FROM ( " + 
            "        SELECT " + Columns.ROW_ID + 
            "             , " + Columns.CATEGORY_CD + 
            "          FROM " + TABLE_NAME;
        
        if (top > 0){
            sQuery = sQuery + 
            "         ORDER BY " + Columns.ROW_ID + " DESC" + 
            "         LIMIT " + top;
        }
            
        sQuery = sQuery + " ) ";
        // @formatter:on

        try {

            cursor = (SQLiteCursor) mDatabase.rawQuery(sQuery, null);
            cursor.moveToFirst();

            if (cursor.getCount() > 0) {

                categoryCount = new CategoryCount();

                categoryCount.setTotal(cursor.getInt(0));
                categoryCount.setGrapp(cursor.getInt(1));
                categoryCount.setProx(cursor.getInt(2));
                categoryCount.setShoot(cursor.getInt(3));
                categoryCount.setCannon(cursor.getInt(4));
                categoryCount.setSnipe(cursor.getInt(5));

            }

        } catch (Exception e) {

            // Log.e(TAG, "RecordDao.getCategoryCount failed - " +
            // e.toString());

        } finally {

            if (cursor != null) {
                cursor.close();
            }

        }

        return categoryCount;

    }

    /********************
     * Insert
     ********************/

    /**
     * レコード追加
     */
    public long insert(Record record) {

        String sDate = "";
        if (record.getInsertDate().equals("")) {
            // 作成日時がなければ現在日付を取得
            Date date = new Date();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
            sDate = sdf.format(date);
        } else {
            // バックアップから復元する場合はファイル内の作成日付を使用
            sDate = record.getInsertDate();
        }

        ContentValues values = new ContentValues();
        values.put(Columns.STAGE_CD, record.getStageCd());
        values.put(Columns.CATEGORY_CD, record.getCategoryCd());
        values.put(Columns.RESULT_CD, record.getResultCd());
        values.put(Columns.INSERT_DATE, sDate);
        values.put(Columns.VERSUS, record.getVersus());
        values.put(Columns.POINT, record.getPoint());
        values.put(Columns.RANK_CD, record.getRankCd());
        values.put(Columns.MEMO, record.getMemo());
        values.put(Columns.EXCLUDE_FLG, record.getExcludeFlg());

        return mDatabase.insert(TABLE_NAME, null, values);

    }

    /********************
     * Update
     ********************/

    /**
     * レコードの更新
     */
    public int update(Record record) {

        long lRet = 0;

        String sWhere = Columns.ROW_ID + " = " + record.getRowId();
        
        ContentValues values = new ContentValues();
        values.put(Columns.MEMO, record.getMemo());
        // values.put(Columns.MEMO, record.getMemoByBase64());
        
        try {

            lRet = mDatabase.update(TABLE_NAME, values, sWhere, null);

        } catch (Exception e) {

            // Log.e(TAG, "RecordDao.deleteAt failed - " + e.toString());

        }

        return (int) lRet;

    }

    /********************
     * Delete
     ********************/

    /**
     * 特定レコードの削除
     * 
     * @param rowId
     * @return 処理件数
     */
    public int deleteAt(int rowId) {

        long lDelete = 0;

        // @formatter:off
        String sWhere = Columns.ROW_ID + " = " + rowId;
        // @formatter:on

        try {

            lDelete = mDatabase.delete(TABLE_NAME, sWhere, null);

        } catch (Exception e) {

            // Log.e(TAG, "RecordDao.deleteAt failed - " + e.toString());

        }

        return (int) lDelete;
    }

    /**
     * 全レコードの削除
     * 
     * @return 処理件数
     */
    public int deleteAll() {

        long lDelete = 0;

        try {

            lDelete = mDatabase.delete(TABLE_NAME, null, null);

        } catch (Exception e) {

            // Log.e(TAG, "RecordDao.deleteAll failed - " + e.toString());

        }

        return (int) lDelete;

    }

}
